﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WF_DataAccess;
using WF_Business;

namespace ExtBusiness.Common
{
    /// <summary>
    /// 
    /// </summary>
    public class InfoManage
    {
        /// <summary>
        /// 获取上传的文件
        /// </summary>
        /// <param name="type"></param>
        /// <param name="nRowNum">显示的记录数</param>
        /// <returns></returns>
        public static DataTable GetFileUploadInfo(string type, int nRowNum)
        {
            string strSql = "select * from (select t.id as id,t.filename title,t.uploadtime as time,t.type as type from xt_uploadfile t where 1 = 1  ";
            if (!string.IsNullOrEmpty(type))
            {
                strSql += string.Format(" and type ='{0}'", type);
            }
            strSql += string.Format(" order by t.uploadtime desc ) where rownum<={0}", nRowNum);
            DataTable dt = null;

            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// Gets the type of the info.
        /// </summary>
        /// <param name="Type">消息类型</param>
        /// <param name="strSmalType">流程名称</param>
        /// <returns></returns>
        public DataTable GetInfoByAllType(string Type, string strSmalType)
        {
            string strSql = @"select * from (select t.id as id,(case when length(t.title)>14 then 
                Substr(t.title,0,14) || '...' else t.title end ) as title,t.time,t.type  
                from st_other t where 1 = 1  ";
            if (!string.IsNullOrEmpty(Type))
            {
                strSql += string.Format(" and type ='{0}'", Type);
            }
            if (!string.IsNullOrEmpty(strSmalType))
            {
                strSql += string.Format(" and SMALLTYPE ='{0}'", strSmalType);
            }
            strSql += " order by t.time desc ) where rownum<=5 ";

            DataTable dt = null;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取信息发布的内容2
        /// </summary>
        /// <param name="type"></param>
        /// <param name="nRowNum">记录数</param>
        /// <returns></returns>
        public static DataTable GetInfoType(string type,int nRowNum)
        {
            string strSql = "select * from (select t.id as id,(case when length(t.title)>14 then Substr(t.title,0,14) || '...' else t.title end ) as title,t.time as time,t.type as type from st_other t where 1 = 1  ";
            if (!string.IsNullOrEmpty(type))
            {
                strSql += string.Format(" and type ='{0}'", type);
            }
            strSql += string.Format(" order by t.time desc ) where rownum<={0}",nRowNum);
            DataTable dt = null;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取信息发布的内容1
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static DataTable GetInfoType(string type)
        {
            string strSql = "select * from (select t.id as id,(case when length(t.title)>14 then Substr(t.title,0,14) || '...' else t.title end ) as title,t.time as time,t.type as type from st_other t where 1 = 1  ";
            if (!string.IsNullOrEmpty(type))
            {
                strSql += string.Format(" and type ='{0}'", type);
            }
            strSql += " order by t.time desc ) where rownum<=5 ";
            DataTable dt = null;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取信息发布的内容3
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static DataTable GetInfoTypeTop(string type)
        {
            string strSql = "select * from (select t.id as id,(case when length(t.title)>30 then Substr(t.title,0,30) || '...' else t.title end ) as title,t.time as time,t.type as type from st_other t where 1 = 1  ";
            if (!string.IsNullOrEmpty(type))
            {
                strSql += string.Format(" and type ='{0}'", type);
            }
            strSql += " order by t.time desc ) where rownum<=5 ";
            DataTable dt = null;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 根据用户信息获取信息发布的内容1
        /// </summary>
        /// <param name="type"></param>
        /// <param name="login_name"></param>
        /// <returns></returns>
        public static DataTable GetInfoType(string type,string login_name)
        {
            string strSql = "select * from (select t.id as id,(case when length(t.title)>18 then Substr(t.title,0,18) || '..' else t.title end ) as title,t.time as time,t.type as type from st_other t,ST_NOTIFYUSER t1 where t.id=t1.sid and t1.login_name='"+login_name+"' and 1 = 1  ";
            if (!string.IsNullOrEmpty(type))
            {
                strSql += string.Format(" and type ='{0}'", type);
            }
            strSql += " order by t.time desc ) where rownum<=10 ";
            DataTable dt = null;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 根据用户信息获取信息发布的内容2
        /// </summary>
        /// <param name="type"></param>
        /// <param name="login_name"></param>
        /// <returns></returns>
        public static DataTable GetInfoTypeTop(string type, string login_name)
        {
            string strSql = "select * from (select t.id as id,(case when length(t.title)>30 then Substr(t.title,0,30) || '...' else t.title end ) as title,t.time as time,t.type as type from st_other ,ST_NOTIFYUSER t1 where t.id=t1.sid and t1.login_name='" + login_name + "' and 1 = 1  ";
            if (!string.IsNullOrEmpty(type))
            {
                strSql += string.Format(" and type ='{0}'", type);
            }
            strSql += " order by t.time desc ) where rownum<=5 ";
            DataTable dt = null;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 根据用户组获取菜单信息
        /// </summary>
        /// <param name="Groupids"></param>
        /// <returns></returns>
        public static DataTable UserPopedom(string Groupids)
        {
            string strSql = "";
            DataTable dt = null;
            if (!string.IsNullOrEmpty(Groupids))
            {
                strSql = string.Format(@"select m_c.* from xt_menu_child m_c,xt_menu_parent m_p where m_c.ancestor=m_p.menu_parent_id
                                        and m_c.menu_child_id in(select menu_child_id from xt_group_menu where right_id in ({0})) order by m_c.show_no", Groupids);
               
                SysParams.OAConnection().RunSql(strSql, out dt);             
            }           
            return dt;
        }

        //新增-LSJ
        public static DataTable MyGetInfoType(string type, int infonum, int nRowNum)
        {
            string strSql = "select * from (select t.id as id,(case when length(t.title)>'" + infonum + "' then Substr(t.title,0,'" + infonum + "') || '...' else t.title end ) as title,t.title as fulltitle,t.time as time,t.type as type from st_other t where 1 = 1  ";
            if (!string.IsNullOrEmpty(type))
            {
                strSql += string.Format(" and type ='{0}'", type);
            }
            strSql += string.Format(" order by t.time desc ) where rownum<={0}", nRowNum);
            DataTable dt = null;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        public static DataTable MyGetInfoType(string type, int infonum, int nRowNum, string loginname)
        {
            string strSql = string.Format(@" select rownum,p.* from (select distinct t.id as id,(case when length(t.title)>'{0}' then Substr(t.title,0,'{0}') || '...' else t.title end ) title,t.title fulltitle,t.time as time,t.type as type,t.userid,readflag,u.user_name from st_other t,ST_NOTIFYUSER t1,st_user u
 where u.userid=t.userid and t.id=t1.sid and t1.LOGIN_NAME='{1}'", infonum, loginname);
            if (!string.IsNullOrEmpty(type))
            {
                strSql += string.Format(" and t.type ='{0}'", type);
            }
            strSql += string.Format(" order by t.time desc ) p  where rownum<={0}", nRowNum);
            DataTable dt = null;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        public static DataTable MyGetInfoFile(string type, int infonum, int nRowNum)
        {
            string selectSQL = "select * from (select (case when length(FILENAME) > '" + infonum + "' then Substr(FILENAME, 0, '" + infonum + "') || '...' else FILENAME end) as title,FILENAME,UPLOADTIME,FILEPATH from xt_uploadfile where TYPE = '" + type + "' order by uploadtime desc) where rownum <= '" + nRowNum + "'";
            DataTable dt = null;
            SysParams.OAConnection().RunSql(selectSQL, out dt);
            return dt;
        }

        public static DataTable MyGetSP(string type, int infonum, int nRowNum)
        {
            string selectSQL = "select * from (select id,title as fulltitle,(case when length(title) > '" + infonum + "' then Substr(title, 0, '" + infonum + "') || '...' else title end) as title from xt_videoinfo where type='" + type + "' order by uptime desc) where rownum <='" + nRowNum + "'";
            DataTable dt = null;
            SysParams.OAConnection().RunSql(selectSQL, out dt);
            return dt;
        }

        public static DataTable MyGetSP_New(string type, int infonum, int nRowNum)
        {
            string selectSQL = "select * from (select id,title as fulltitle,(case when length(title) > '" + infonum + "' then Substr(title, 0, '" + infonum + "') || '...' else title end) as title from xt_videoinfo where type='" + type + "' order by show_no desc) where rownum <='" + nRowNum + "'";
            DataTable dt = null;
            SysParams.OAConnection().RunSql(selectSQL, out dt);
            return dt;
        }
    }
}
